package db;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

// import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;

public class JdbcUserDao extends JdbcDaoSupport implements UserDao {
	
	// JDBC DataSource + SimpleJdbcTemplate 
	/*
	private SimpleJdbcTemplate jdbcTemplate;
	public void setJdbcTemplate(SimpleJdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	*/
	
	// Connection Pool DataSource + JdbcTemplate
	/*
	private JdbcTemplate jdbcTemplate;
	public void setJdbcTemplate( JdbcTemplate jdbcTemplate ) {
		this.jdbcTemplate = jdbcTemplate;
	}	
	*/
	
	@Override
	public List<User> getUsers() {
		String sql = "select * from dbtest";
		return getJdbcTemplate().query(
			sql,
			new ParameterizedRowMapper <User> () {
				@Override
				public User mapRow( ResultSet rs, int rowNum ) throws SQLException {
					User user = new User();
					user.setName( rs.getString( "name" ) );
					user.setAge( rs.getInt( "age" ) );
					user.setAddress( rs.getString( "address" ) );
					user.setTel( rs.getString( "tel" ) );					
					return user;
				}				
			}
		);		
	}

	@Override
	public void updateUser(User user) {
		String sql 
			= "update dbtest set age=?, address=?, tel=? where name=?";
		getJdbcTemplate().update( 
			sql, 
			user.getAge(),
			user.getAddress(),
			user.getTel(),
			user.getName()
		);		
	}


	@Override
	public void deleteByName(String name) {
		String sql = "delete from dbtest where name=?";
		getJdbcTemplate().update(
			sql, 
			name
		);		
	}

	@Override
	public int getCount() {
		String sql = "select count( * ) from dbtest";		
		return getJdbcTemplate().queryForInt( sql );
	}
	
	@Override
	public Member getMemberById(String id) {
		String sql = "select * from member where id=?";
		return getJdbcTemplate().queryForObject(
			sql,
			new Object[] {id},
			new ParameterizedRowMapper <Member>() {
				@Override
				public Member mapRow(ResultSet rs, int rowNum) throws SQLException {
					Member member = new Member();
					member.setId( rs.getString( "id" ) );
					member.setPasswd( rs.getString( "passwd" ) );
					member.setName( rs.getString( "name" ) );
					member.setJumin1( rs.getString( "jumin1" ) );
					member.setJumin2( rs.getString( "jumin2" ) );
					member.setTel( rs.getString( "tel" ) );
					member.setEmail( rs.getString( "email" ) );
					member.setReg_date( rs.getTimestamp( "reg_date") );					
					return member;
				}				
			}
		);
	}
	
	@Override
	public User getUserByName(String name) {
		String sql ="select * from dbtest where name=?";		
		return getJdbcTemplate().queryForObject(
			sql,
			new Object[] {name},
			new ParameterizedRowMapper<User> () {
				@Override
				public User mapRow(ResultSet rs, int rowNum) throws SQLException {
					User user = new User();
					user.setName( rs.getString( "name" ) );
					user.setAge( rs.getInt( "age" ) );
					user.setAddress( rs.getString( "address" ) );
					user.setTel( rs.getString( "tel" ) );
					return user;
				}				
			}
		);
	}

	@Override
	public void deleteAll() {
		getJdbcTemplate().update( "delete from dbtest" );
	}	
	
	@Override
	public void addUser( User user ) {
		String sql = "insert into dbtest ( name, age, address, tel ) values( ?, ?, ?, ? )";
		getJdbcTemplate().update( 
			sql, 
			user.getName(), 
			user.getAge(), 
			user.getAddress(), 
			user.getTel() 
		);
	}


} // class






